-- 银行信息管理表结构
-- 用于存储银行基础信息、BIN码映射和名称别名

-- 1. 银行基础信息表
CREATE TABLE [dbo].[BankInfo] (
    [Id] INT IDENTITY(1,1) PRIMARY KEY,
    [StandardName] NVARCHAR(100) NOT NULL UNIQUE, -- 标准银行名称
    [ShortName] NVARCHAR(50), -- 简称
    [EnglishName] NVARCHAR(100), -- 英文名称
    [BankCode] NVARCHAR(20), -- 银行代码
    [CardLengthRule] NVARCHAR(50), -- 卡号长度规则，如"16-19位"、"16位"等
    [IsActive] BIT NOT NULL DEFAULT 1, -- 是否启用
    [IsCustom] BIT NOT NULL DEFAULT 0, -- 是否为自定义银行
    [CreatedAt] DATETIME2 NOT NULL DEFAULT GETDATE(),
    [UpdatedAt] DATETIME2 NOT NULL DEFAULT GETDATE(),
    [CreatedBy] NVARCHAR(50),
    [UpdatedBy] NVARCHAR(50),
    [Remark] NVARCHAR(500) -- 备注
);

-- 2. 银行BIN码映射表
CREATE TABLE [dbo].[BankBinMapping] (
    [Id] INT IDENTITY(1,1) PRIMARY KEY,
    [BankId] INT NOT NULL,
    [BinCode] NVARCHAR(10) NOT NULL, -- BIN码（通常6位）
    [CardType] NVARCHAR(50), -- 卡片类型（借记卡、信用卡等）
    [IsActive] BIT NOT NULL DEFAULT 1,
    [CreatedAt] DATETIME2 NOT NULL DEFAULT GETDATE(),
    [UpdatedAt] DATETIME2 NOT NULL DEFAULT GETDATE(),
    [CreatedBy] NVARCHAR(50),
    [UpdatedBy] NVARCHAR(50),
    [Remark] NVARCHAR(500),
    CONSTRAINT [FK_BankBinMapping_BankInfo] FOREIGN KEY ([BankId]) REFERENCES [dbo].[BankInfo]([Id]) ON DELETE CASCADE,
    CONSTRAINT [UK_BankBinMapping_BinCode] UNIQUE ([BinCode])
);

-- 3. 银行名称别名表
CREATE TABLE [dbo].[BankNameAlias] (
    [Id] INT IDENTITY(1,1) PRIMARY KEY,
    [BankId] INT NOT NULL,
    [AliasName] NVARCHAR(100) NOT NULL, -- 别名
    [AliasType] NVARCHAR(20) NOT NULL DEFAULT 'COMMON', -- 别名类型：COMMON(通用)、SHORT(简称)、ENGLISH(英文)、LEGACY(历史名称)
    [IsActive] BIT NOT NULL DEFAULT 1,
    [CreatedAt] DATETIME2 NOT NULL DEFAULT GETDATE(),
    [UpdatedAt] DATETIME2 NOT NULL DEFAULT GETDATE(),
    [CreatedBy] NVARCHAR(50),
    [UpdatedBy] NVARCHAR(50),
    [Remark] NVARCHAR(500),
    CONSTRAINT [FK_BankNameAlias_BankInfo] FOREIGN KEY ([BankId]) REFERENCES [dbo].[BankInfo]([Id]) ON DELETE CASCADE,
    CONSTRAINT [UK_BankNameAlias_Name] UNIQUE ([AliasName])
);

-- 4. 银行信息变更日志表
CREATE TABLE [dbo].[BankInfoChangeLog] (
    [Id] INT IDENTITY(1,1) PRIMARY KEY,
    [BankId] INT NOT NULL,
    [ChangeType] NVARCHAR(20) NOT NULL, -- 变更类型：CREATE、UPDATE、DELETE、ACTIVATE、DEACTIVATE
    [TableName] NVARCHAR(50) NOT NULL, -- 变更的表名
    [FieldName] NVARCHAR(50), -- 变更的字段名
    [OldValue] NVARCHAR(MAX), -- 原值
    [NewValue] NVARCHAR(MAX), -- 新值
    [ChangeReason] NVARCHAR(500), -- 变更原因
    [CreatedAt] DATETIME2 NOT NULL DEFAULT GETDATE(),
    [CreatedBy] NVARCHAR(50),
    CONSTRAINT [FK_BankInfoChangeLog_BankInfo] FOREIGN KEY ([BankId]) REFERENCES [dbo].[BankInfo]([Id])
);

-- 创建索引
CREATE INDEX [IX_BankInfo_StandardName] ON [dbo].[BankInfo] ([StandardName]);
CREATE INDEX [IX_BankInfo_IsActive] ON [dbo].[BankInfo] ([IsActive]);
CREATE INDEX [IX_BankInfo_IsCustom] ON [dbo].[BankInfo] ([IsCustom]);

CREATE INDEX [IX_BankBinMapping_BankId] ON [dbo].[BankBinMapping] ([BankId]);
CREATE INDEX [IX_BankBinMapping_BinCode] ON [dbo].[BankBinMapping] ([BinCode]);
CREATE INDEX [IX_BankBinMapping_IsActive] ON [dbo].[BankBinMapping] ([IsActive]);

CREATE INDEX [IX_BankNameAlias_BankId] ON [dbo].[BankNameAlias] ([BankId]);
CREATE INDEX [IX_BankNameAlias_AliasName] ON [dbo].[BankNameAlias] ([AliasName]);
CREATE INDEX [IX_BankNameAlias_IsActive] ON [dbo].[BankNameAlias] ([IsActive]);

CREATE INDEX [IX_BankInfoChangeLog_BankId] ON [dbo].[BankInfoChangeLog] ([BankId]);
CREATE INDEX [IX_BankInfoChangeLog_CreatedAt] ON [dbo].[BankInfoChangeLog] ([CreatedAt]);

-- 添加触发器用于自动更新UpdatedAt字段
CREATE TRIGGER [TR_BankInfo_UpdatedAt] ON [dbo].[BankInfo]
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE [dbo].[BankInfo]
    SET [UpdatedAt] = GETDATE()
    FROM [dbo].[BankInfo] b
    INNER JOIN inserted i ON b.[Id] = i.[Id];
END;

CREATE TRIGGER [TR_BankBinMapping_UpdatedAt] ON [dbo].[BankBinMapping]
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE [dbo].[BankBinMapping]
    SET [UpdatedAt] = GETDATE()
    FROM [dbo].[BankBinMapping] b
    INNER JOIN inserted i ON b.[Id] = i.[Id];
END;

CREATE TRIGGER [TR_BankNameAlias_UpdatedAt] ON [dbo].[BankNameAlias]
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE [dbo].[BankNameAlias]
    SET [UpdatedAt] = GETDATE()
    FROM [dbo].[BankNameAlias] b
    INNER JOIN inserted i ON b.[Id] = i.[Id];
END;

-- 添加注释
EXEC sp_addextendedproperty 'MS_Description', '银行基础信息表', 'SCHEMA', 'dbo', 'TABLE', 'BankInfo';
EXEC sp_addextendedproperty 'MS_Description', '银行ID', 'SCHEMA', 'dbo', 'TABLE', 'BankInfo', 'COLUMN', 'Id';
EXEC sp_addextendedproperty 'MS_Description', '标准银行名称', 'SCHEMA', 'dbo', 'TABLE', 'BankInfo', 'COLUMN', 'StandardName';
EXEC sp_addextendedproperty 'MS_Description', '银行简称', 'SCHEMA', 'dbo', 'TABLE', 'BankInfo', 'COLUMN', 'ShortName';
EXEC sp_addextendedproperty 'MS_Description', '英文名称', 'SCHEMA', 'dbo', 'TABLE', 'BankInfo', 'COLUMN', 'EnglishName';
EXEC sp_addextendedproperty 'MS_Description', '银行代码', 'SCHEMA', 'dbo', 'TABLE', 'BankInfo', 'COLUMN', 'BankCode';
EXEC sp_addextendedproperty 'MS_Description', '卡号长度规则', 'SCHEMA', 'dbo', 'TABLE', 'BankInfo', 'COLUMN', 'CardLengthRule';
EXEC sp_addextendedproperty 'MS_Description', '是否启用', 'SCHEMA', 'dbo', 'TABLE', 'BankInfo', 'COLUMN', 'IsActive';
EXEC sp_addextendedproperty 'MS_Description', '是否为自定义银行', 'SCHEMA', 'dbo', 'TABLE', 'BankInfo', 'COLUMN', 'IsCustom';

EXEC sp_addextendedproperty 'MS_Description', '银行BIN码映射表', 'SCHEMA', 'dbo', 'TABLE', 'BankBinMapping';
EXEC sp_addextendedproperty 'MS_Description', 'BIN码（通常6位）', 'SCHEMA', 'dbo', 'TABLE', 'BankBinMapping', 'COLUMN', 'BinCode';
EXEC sp_addextendedproperty 'MS_Description', '卡片类型', 'SCHEMA', 'dbo', 'TABLE', 'BankBinMapping', 'COLUMN', 'CardType';

EXEC sp_addextendedproperty 'MS_Description', '银行名称别名表', 'SCHEMA', 'dbo', 'TABLE', 'BankNameAlias';
EXEC sp_addextendedproperty 'MS_Description', '别名', 'SCHEMA', 'dbo', 'TABLE', 'BankNameAlias', 'COLUMN', 'AliasName';
EXEC sp_addextendedproperty 'MS_Description', '别名类型', 'SCHEMA', 'dbo', 'TABLE', 'BankNameAlias', 'COLUMN', 'AliasType';

EXEC sp_addextendedproperty 'MS_Description', '银行信息变更日志表', 'SCHEMA', 'dbo', 'TABLE', 'BankInfoChangeLog';
EXEC sp_addextendedproperty 'MS_Description', '变更类型', 'SCHEMA', 'dbo', 'TABLE', 'BankInfoChangeLog', 'COLUMN', 'ChangeType';